package com.wm.task.dao;

import com.wm.task.util.DbHelper;
import com.wm.task.vo.Movie;
import com.wm.task.vo.ScoreRecord;
import com.wm.task.vo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    private List<Movie> movieList = new ArrayList<>();
    private List<ScoreRecord> scoreRecords = new ArrayList<>();

    public List<Movie> movieList() {
        String sql = "SELECT movie_info.id,title,original_title,release_year,duration,director.`name`," +
                "`language`.`name`,country.`name`,rating,rating_count,plot_summary,movie_type.`name` \n" +
                "FROM movie_info \n" +
                "LEFT JOIN director ON director.id = movie_info.director_id \n" +
                "LEFT JOIN `language` ON `language`.id = movie_info.language_id\n" +
                "LEFT JOIN country ON country.id = movie_info.country_id\n" +
                "LEFT JOIN movie_type ON movie_type.id = movie_info.movie_type_id;";
        Connection connection = DbHelper.getDate();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                movieList.add(new Movie(rs.getInt("id"), rs.getString("title"),
                        rs.getString("original_title"), rs.getInt("release_year"),
                        rs.getInt("duration"), rs.getString("director.name"),
                        rs.getString("language.name"), rs.getString("country.name"),
                        rs.getFloat("rating"), rs.getInt("rating_count"),
                        rs.getString("plot_summary"), rs.getString("movie_type.name")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("列表问题");
        } finally {
            DbHelper.close(connection);
        }
        return movieList;
    }

    public ScoreRecord score(User user, Movie movie, float rating) {
        ScoreRecord scoreRecord = null;
        String sql = "SELECT * FROM score_record WHERE user_id = ? AND movie = ?";
        Connection connection = DbHelper.getDate();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, user.getId());
            ps.setString(2, movie.getTitle());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                scoreRecord = new ScoreRecord(rs.getTime("time"), rs.getString("movie"),
                        rs.getFloat("score"));
            } else {
                sql = "INSERT INTO score_record (movie,score,user_id) VALUES (?,?,?);";
                DbHelper.update(sql, movie.getTitle(), rating, user.getId());
                sql = "UPDATE movie_info SET rating_count = ? WHERE id = ?;";
                DbHelper.update(sql, movie.getRatingCount() + 1, movie.getId());


            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbHelper.close(connection);
        }
        return scoreRecord;

    }

    public List<ScoreRecord> scoreRecord() {
        String sql = "SELECT * FROM score_record ;";
        Connection connection = DbHelper.getDate();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                scoreRecords.add(new ScoreRecord(rs.getTime("time"), rs.getString("movie"),
                        rs.getFloat("score")));
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbHelper.close(connection);
        }
        return scoreRecords;
    }
}